package com.crm.business.crm.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.lang.PatternPool;
import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.crm.business.admin.service.AdminService;
import com.crm.business.crm.service.*;
import com.crm.common.crm.constant.FieldEnum;
import com.crm.common.ka.core.common.R;
import com.crm.common.ka.core.feign.admin.entity.AdminMessage;
import com.crm.business.utils.AuthUtil;
import com.crm.common.crm.common.CrmExcelUtil;
import com.crm.common.crm.common.CrmVerify;
import com.crm.common.crm.constant.CrmCodeEnum;
import com.crm.common.crm.constant.CrmEnum;
import com.crm.common.crm.entity.BO.*;
import com.crm.common.crm.entity.PO.*;
import com.crm.common.crm.entity.VO.CrmModelFiledVO;
import com.crm.common.ka.core.common.Result;
import com.crm.common.ka.core.common.SystemCodeEnum;
import com.crm.common.ka.core.common.cache.AdminCacheKey;
import com.crm.common.ka.core.exception.CrmException;
import com.crm.common.ka.core.feign.admin.entity.AdminMessageEnum;
import com.crm.common.ka.core.feign.crm.entity.SimpleCrmEntity;
import com.crm.common.ka.core.redis.Redis;
import com.crm.common.ka.core.redis.RedisImpl;
import com.crm.business.servlet.ApplicationContextHolder;
import com.crm.common.ka.core.servlet.upload.UploadConfig;
import com.crm.business.utils.BaseUtil;
import com.crm.business.utils.UserUtil;
import com.crm.common.utils.DateUtils;
import com.crm.common.utils.StringUtils;
import com.crm.common.utils.spring.SpringUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.elasticsearch.action.search.SearchRequest;
import org.elasticsearch.action.search.SearchResponse;
import org.elasticsearch.client.RequestOptions;
import org.elasticsearch.common.unit.TimeValue;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.elasticsearch.search.SearchHit;
import org.elasticsearch.search.builder.SearchSourceBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.core.task.TaskExecutor;
import org.springframework.data.elasticsearch.core.ElasticsearchRestTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.Executor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicReference;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

@Service
@Slf4j
public class CrmUploadExcelServiceImpl implements CrmUploadExcelService {

    @Autowired
    private AdminService adminService;

    @Autowired
    private Redis redis;

    @Autowired
    private RedisImpl redisImpl;

    @Autowired
    private ElasticsearchRestTemplate restTemplate;

    @Autowired
    private ICrmFieldService fieldService;

    private String uploadPath = SpringUtils.getBean(UploadConfig.class).getLocal().getUploadPath().get("0");


    /**
     * 上传excel可查询时间
     */
    protected static final int UPLOAD_EXCEL_EXIST_TIME = 7200;


    @Qualifier("importExecutor")
    @Autowired
    private Executor executor;




    /**
     * 导入excel
     */
    @Override
    public Long uploadExcel(MultipartFile file, UploadExcelBO uploadExcelBO) {
        String filePath = getFilePath(file);
        uploadExcelBO.setFilePath(filePath);
        AdminMessage adminMessage = new AdminMessage();
        adminMessage.setCreateUser(UserUtil.getUserId());
        adminMessage.setRecipientUser(UserUtil.getUserId());
        adminMessage.setLabel(6);
        UploadService uploadService;
        switch (uploadExcelBO.getCrmEnum()) {
            case CUSTOMER:
                uploadService = new CustomerUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_CUSTOMER_IMPORT.getType());
                break;
            case LEADS:
                uploadService = new LeadsUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_LEADS_IMPORT.getType());
                break;
            case CONTACTS:
                uploadService = new ContactsUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_CONTACTS_IMPORT.getType());
                break;
            case PRODUCT:
                uploadService = new ProductUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_PRODUCT_IMPORT.getType());
                break;
            case RECEIVABLES:
                uploadService = new BillReceivablesUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_BILL_RECEIVABLES_IMPORT.getType());
                break;
            case STUDENT:
                uploadService = new StudentUploadService(uploadExcelBO);
                adminMessage.setType(AdminMessageEnum.CRM_STUDENT_IMPORT.getType());
                break;
            default:
                throw new CrmException(SystemCodeEnum.SYSTEM_NO_VALID);
        }
        Long messageId = adminService.saveOrUpdateMessage(adminMessage);
        uploadExcelBO.setMessageId(messageId);
        redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + messageId.toString(), UPLOAD_EXCEL_EXIST_TIME, 0);
//        TaskExecutor taskExecutor = SpringUtils.getBean(TaskExecutor.class);
        executor.execute(uploadService);
        return messageId;
    }

    /**
     * 获取临时文件路径,用完记得删除
     *
     * @param file file
     * @return path
     */
    private String getFilePath(MultipartFile file) {
        String dirPath = FileUtil.getTmpDirPath();
        try {
            InputStream inputStream = file.getInputStream();
            File fromStream = FileUtil.writeFromStream(inputStream, dirPath + "/" + IdUtil.simpleUUID() + file.getOriginalFilename());
            return fromStream.getAbsolutePath();
        } catch (IOException e) {
            throw new CrmException(SystemCodeEnum.SYSTEM_UPLOAD_FILE_ERROR);
        }
    }

    public abstract class UploadService implements Runnable {

        protected volatile List<List<Object>> errorList = new ArrayList<>();

        protected volatile List<CrmModelFiledVO> fieldList = new ArrayList<>();

        protected volatile List<CrmModelFiledVO> fixedFieldList = new ArrayList<>();

        protected volatile List<CrmModelFiledVO> uniqueList = new ArrayList<>();

        /**
         * 导入数量
         */
        protected volatile Integer num = -2;

        /**
         * 修改数量
         */
        protected volatile Integer updateNum = 0;

        protected volatile List<Integer> isNullList = new ArrayList<>();

        protected volatile boolean templateErr = false;

        protected volatile JSONObject kv = new JSONObject();

        public abstract void importExcel();

        public abstract UploadExcelBO getUploadExcelBO();

        @Override
        public void run() {
            boolean exists = redis.exists(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId());
            if (!exists) {
                return;
            }
            try {
                UserUtil.setUser(getUploadExcelBO().getUserInfo());
                importExcel();
                restTemplate.refresh(getUploadExcelBO().getCrmEnum().getIndex());
            } catch (Exception e) {
                log.error("导入出现错误", e);
            } finally {
                redis.del(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId());
                UserUtil.removeUser();
            }

            AdminMessage adminMessage = adminService.getMessageById(getUploadExcelBO().getMessageId());
            adminMessage.setTitle(String.valueOf(Math.max(num, 0)));
            adminMessage.setContent((errorList.size() - 2) + "," + updateNum);
            if (errorList.size() > 2) {
                File file = new File(uploadPath + "/excel/" + BaseUtil.getDate() + "/" + IdUtil.simpleUUID() + ".xlsx");
                BigExcelWriter writer = ExcelUtil.getBigWriter(file);
                CellStyle textStyle = writer.getWorkbook().createCellStyle();
                DataFormat format = writer.getWorkbook().createDataFormat();
                textStyle.setDataFormat(format.getFormat("@"));
                for (int i = 0; i < errorList.get(1).size(); i++) {
                    writer.setColumnWidth(i, 20);
                    writer.getSheet().setDefaultColumnStyle(i, textStyle);
                }
                writer.merge(errorList.get(1).size() - 1, errorList.remove(0).get(0));
                writer.setDefaultRowHeight(20);
                Cell cell = writer.getCell(0, 0);
                CellStyle cellStyle = cell.getCellStyle();
                cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                Font font = writer.createFont();
                font.setBold(true);
                font.setFontHeightInPoints((short) 16);
                cellStyle.setFont(font);
                cell.setCellStyle(cellStyle);
                // 一次性写出内容，使用默认样式
                writer.write(errorList);
                // 关闭writer，释放内存
                writer.close();
                adminMessage.setTypeId(null);
                //将错误信息的excel保存七天 604800 七天的秒数
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + "file:" + adminMessage.getMessageId().toString(), 604800, file.getAbsolutePath());
            }
            adminService.saveOrUpdateMessage(adminMessage);
            FileUtil.del(getUploadExcelBO().getFilePath());
        }


        /**
         * 查询导入顺序
         *
         * @param rowList 行数据
         * @author zhangzhiwei
         */
        protected void queryExcelHead(List<Object> rowList) {
            switch (getUploadExcelBO().getCrmEnum()) {
                case LEADS: {
                    fieldList = SpringUtils.getBean(ICrmLeadsService.class).queryField(null);
                    break;
                }
                case CUSTOMER: {
                    fieldList = SpringUtils.getBean(ICrmCustomerService.class).queryField(null);
                    break;
                }
                case CONTACTS: {
                    fieldList = SpringUtils.getBean(ICrmContactsService.class).queryField(null);
                    //追加新字段
                    List<CrmModelFiledVO> zjfieldList = new ArrayList<>();
                    Iterator<CrmModelFiledVO> iterator = fieldList.iterator();
                    int i=0;
                    while (iterator.hasNext()){
                        CrmModelFiledVO c = iterator.next();
                        if (i>=1&&i<=5){
                            iterator.remove();
                            zjfieldList.add(c);
                        }
                        i++;
                    }
                    fieldList.addAll(zjfieldList);
                    break;
                }
                case PRODUCT: {
                    fieldList = SpringUtils.getBean(ICrmProductService.class).queryField(null);
                    break;
                }
                case RECEIVABLES:{
                    fieldList = SpringUtils.getBean(ICrmReceivablesService.class).queryExportField(null);
                    break;
                }
                case STUDENT: {
                    fieldList = SpringUtils.getBean(ICrmStudentService.class).queryField(null);
                    fieldList.removeIf(item -> "source".equals(item.getFieldName()));
                    fieldList.removeIf(item -> "ownerSeas".equals(item.getFieldName()));
                    break;
                }
            }
            if(getUploadExcelBO().getCrmEnum().getType() == 3){
                fieldList.removeIf(record -> Arrays.asList("file", "user", "structure").contains(record.getFormType()));
            }else{
                fieldList.removeIf(record -> Arrays.asList("file", "checkbox", "user", "structure","single_user").contains(record.getFormType()));
            }
            HashMap<String, String> nameMap = new HashMap<>();
            HashMap<String, Integer> isNullMap = new HashMap<>();
            fieldList.forEach(filed -> {
                //判断线索营销讲师取消必填
                //TODO 线索导入判断 是否加微信 必填判断
                if("add_wechat".equals(filed.getFieldName())){
                    filed.setIsNull(0);
                }
                if (Objects.equals(1, filed.getFieldType()) && !"mapAddress".equals(filed.getFieldName()) && !"mapCity".equals(filed.getFieldName()) && !"mapWifsuu".equals(filed.getFieldName())) {
                    fixedFieldList.add(filed);
                }
                if (Objects.equals(1, filed.getIsUnique())) {
                    uniqueList.add(filed);
                }
                if ((getUploadExcelBO().getCrmEnum() == CrmEnum.CUSTOMER) && "mapAddress".equals(filed.getFieldName())) {
                    nameMap.put("省", "province");
                    isNullMap.put("省", 0);
                    nameMap.put("市", "city");
                    isNullMap.put("市", 0);
                    nameMap.put("区", "site");
                    isNullMap.put("区", 0);


                } else if ((getUploadExcelBO().getCrmEnum() == CrmEnum.CONTACTS) && ("mapAddress".equals(filed.getFieldName()) || "mapCity".equals(filed.getFieldName()) || "mapWifsuu".equals(filed.getFieldName()))) {
                    nameMap.put("省", "province");
                    isNullMap.put("省", 0);
                    nameMap.put("市", "city");
                    isNullMap.put("市", 0);
                    nameMap.put("区", "site");
                    isNullMap.put("区", 0);
                    //目前就业城市
                    nameMap.put("目前就业省", "currentProvince");
                    isNullMap.put("目前就业省", 0);
                    nameMap.put("目前就业市", "currentCity");
                    isNullMap.put("目前就业市", 0);
                    nameMap.put("目前就业区", "currentSite");
                    isNullMap.put("目前就业区", 0);
                    //期望就业城市
                    nameMap.put("期望就业省", "qiWangProvince");
                    isNullMap.put("期望就业省", 0);
                    nameMap.put("期望就业市", "qiWangCity");
                    isNullMap.put("期望就业市", 0);
                    nameMap.put("期望就业区", "qiWangSite");
                    isNullMap.put("期望就业区", 0);
                } else if(FieldEnum.LEVEL_LINKAGE.getType().equals(filed.getType())){
                    boolean isNull = Objects.equals(1, filed.getIsNull());
                    nameMap.put(filed.getName() + (isNull ? "(*)" : "") + ("(每级之间请用“|”分割)"), filed.getFieldName());
                    isNullMap.put(filed.getName() + (isNull ? "(*)" : "") + ("(每级之间请用“|”分割)"), filed.getIsNull());
                } else {
                    boolean isNull = Objects.equals(1, filed.getIsNull());
                    nameMap.put(filed.getName() + (isNull ? "(*)" : ""), filed.getFieldName());
                    isNullMap.put(filed.getName() + (isNull ? "(*)" : ""), filed.getIsNull());
                }
            });
            List<String> nameList = new ArrayList<>(nameMap.keySet());
            log.info("CrmUploadExcelServiceImpl.queryExcelHead.nameList:{}",JSONObject.toJSONString(nameList));
            log.info("CrmUploadExcelServiceImpl.queryExcelHead.rowList:{}",JSONObject.toJSONString(rowList));
            log.info("CrmUploadExcelServiceImpl.queryExcelHead.templateErr.result:{}",nameList.size() != rowList.size() || !nameList.containsAll(rowList));
            if (nameList.size() != rowList.size() || !nameList.containsAll(rowList)) {
                templateErr = true;
            } else {
                for (int i = 0; i < rowList.size(); i++) {
                    kv.put(nameMap.get(rowList.get(i).toString()), i);
                    if (1 == isNullMap.get(rowList.get(i).toString())) {
                        isNullList.add(i);
                    }
                }
            }
            rowList.add(0, "错误原因");
            errorList.add(rowList);
        }



        /**
         * 查询唯一数据
         *
         * @return data
         */
        protected List<CrmPutinMoney> uniqueMapListMonery(List<CrmModelFiledVO> uniqueList) {
            if (uniqueList.size() == 0) {
                return new ArrayList<>();
            }
            boolean b = false;
            for (CrmModelFiledVO crmField : uniqueList) {
                Object value = crmField.getValue();
                if (ObjectUtil.isNotEmpty(value)) {
                    b = true;
                }
            }
            if(b){
                CrmPutinMoney crmPutinMoney = new CrmPutinMoney();
                for(CrmModelFiledVO vo : uniqueList){
                    if("putinPlatform".equals(vo.getFieldName()) && ObjectUtil.isNotEmpty(vo.getValue())){
                        crmPutinMoney.setPutinPlatform(String.valueOf(vo.getValue()));
                    }
                    if("coursePeriods".equals(vo.getFieldName()) && ObjectUtil.isNotEmpty(vo.getValue())){
                        crmPutinMoney.setCoursePeriods(String.valueOf(vo.getValue()));
                    }
                    if("putinDate".equals(vo.getFieldName()) && ObjectUtil.isNotEmpty(vo.getValue())){
                        crmPutinMoney.setPutinDate(DateUtil.parse(String.valueOf(vo.getValue()), DatePattern.NORM_DATE_FORMAT));
                    }
                }
                List<CrmPutinMoney> list = SpringUtils.getBean(ICrmPutinMoneyService.class).queryNewList(crmPutinMoney);
                return list;
            }

            return new ArrayList<>();
        }
        /**
         * 查询唯一数据
         *
         * @return data
         */
        protected List<Map<String, Object>> uniqueMapList(List<CrmModelFiledVO> uniqueList) {
            if (uniqueList.size() == 0) {
                return new ArrayList<>();
            }
            boolean b = false;
            for(CrmModelFiledVO crmField : uniqueList){
                Object value = crmField.getValue();
                if (ObjectUtil.isNotEmpty(value)) {
                    b = true;
                }
            }
            //如果 唯一字段中的值都为空 则不进行唯一性判断
            if(b){
                SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
                CrmEnum crmEnum = getUploadExcelBO().getCrmEnum();
                sourceBuilder.fetchSource(new String[]{crmEnum.getTable() + "Id", "batchId", "ownerUserId"}, null);
                sourceBuilder.size(2);
                sourceBuilder.timeout(new TimeValue(60, TimeUnit.SECONDS));
                BoolQueryBuilder queryBuilder = QueryBuilders.boolQuery();
                uniqueList.forEach(crmField -> {
                    Object value = crmField.getValue();
                    if (ObjectUtil.isNotEmpty(value)) {
                        if (Objects.equals(crmField.getType(), 4) && value instanceof Long) {
                            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) value);
                            crmField.setValue(DateUtil.formatDate(date));
                        }
                        queryBuilder.should(QueryBuilders.termQuery(crmField.getFieldName(), crmField.getValue()));
                    }
                });
                queryBuilder.minimumShouldMatch(1);
                sourceBuilder.query(queryBuilder);
                SearchRequest searchRequest = new SearchRequest(crmEnum.getIndex());
                searchRequest.types("_doc");
                searchRequest.source(sourceBuilder);
                try {
                    SearchResponse searchResponse = restTemplate.getClient().search(searchRequest, RequestOptions.DEFAULT);
                    List<Map<String, Object>> mapList = new ArrayList<>();
                    for (SearchHit hit : searchResponse.getHits().getHits()) {
                        mapList.add(hit.getSourceAsMap());
                    }
                    return mapList;
                } catch (IOException e) {
                    throw new CrmException(SystemCodeEnum.SYSTEM_ERROR);
                }
            }else{
                return new ArrayList<>();
            }
        }

        /**
         * 将自定义字段组装成想要的格式
         *
         * @param rowList 行数据
         * @return 自定义字段数组
         */
        protected List<CrmModelFiledVO> addFieldArray(List<Object> rowList) {
            List<CrmModelFiledVO> array = new ArrayList<>();
            fieldList.forEach(record -> {
                if (record.getFieldType().equals(0) || record.getFieldType().equals(2)) {
                    Integer columnsNum = kv.getInteger(record.getFieldName());
                    Object value = rowList.get(columnsNum);
                    if (Objects.equals(record.getType(), 4) && value instanceof Long) {
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) value);
                        record.setValue(DateUtil.formatDate(date));
                    } else {
                        record.setValue(value);
                    }
                    array.add(record);
                }
            });
            return array;
        }
    }


    public class CustomerUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private CustomerUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel() {

            ICrmCustomerPoolRelationService poolRelationService = SpringUtils.getBean(ICrmCustomerPoolRelationService.class);

            Map<String, List<String>> areaMap = CrmExcelUtil.getAreaMap();
            ExcelUtil.readBySax(uploadExcelBO.getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size() - 1; i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num >= 10000) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        try {
                            for (Integer integer : isNullList) {
                                if (ObjectUtil.isEmpty(rowList.get(integer))) {
                                    rowList.add(0, "必填字段未填写");
                                    errorList.add(rowList);
                                    return;
                                }
                            }
                            for (CrmModelFiledVO record : uniqueList) {
                                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                                record.setValue(value);
                            }
                            List<CrmCustomer> customerList = uniqueMapList(uniqueList).stream().map(map -> BeanUtil.mapToBean(map, CrmCustomer.class, true)).collect(Collectors.toList());
                            boolean isUpdate = false;
                            if (customerList.size() > 0) {
                                if (Objects.equals(2, getUploadExcelBO().getRepeatHandling())) {
                                    return;
                                }
                                if (customerList.size() > 1) {
                                    rowList.add(0, "数据与多条唯一性字段重复");
                                    errorList.add(rowList);
                                    return;
                                }
                                if (getUploadExcelBO().getPoolId() != null) {
                                    Integer count = poolRelationService.lambdaQuery()
                                            .eq(CrmCustomerPoolRelation::getPoolId, getUploadExcelBO().getPoolId())
                                            .eq(CrmCustomerPoolRelation::getCustomerId, customerList.get(0).getCustomerId()).count();
                                    if (count == 0) {
                                        rowList.add(0, "重复数据不在当前公海，无权覆盖");
                                        errorList.add(rowList);
                                        return;
                                    }
                                }
                                isUpdate = true;
                            }
                            String province = ObjectUtil.isEmpty(rowList.get(kv.getInteger("province"))) ? "" : rowList.get(kv.getInteger("province")).toString();
                            String city = ObjectUtil.isEmpty(rowList.get(kv.getInteger("city"))) ? "" : rowList.get(kv.getInteger("city")).toString();
                            String site = ObjectUtil.isEmpty(rowList.get(kv.getInteger("site"))) ? "" : rowList.get(kv.getInteger("site")).toString();
                            if (StrUtil.isNotEmpty(city) && (!areaMap.containsKey(province) || !areaMap.get(province).contains(city))) {
                                rowList.add(0, "省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }
                            if (StrUtil.isNotEmpty(site) && (!areaMap.containsKey(city) || !areaMap.get(city).contains(site))) {
                                rowList.add(0, "省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }
                            CrmModelSaveBO object = new CrmModelSaveBO();
                            JSONObject entityObject = new JSONObject();
                            fixedFieldList.forEach(field -> {
                                String fieldName = field.getFieldName();
                                entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                            });
                            entityObject.fluentPut("address", StrUtil.isNotEmpty(province) ? province + "," + city + "," + site : "")
                                    .fluentPut("ownerUserId", getUploadExcelBO().getOwnerUserId());
                            if (customerList.size() == 1) {
                                CrmCustomer customer = customerList.get(0);
                                boolean auth = AuthUtil.isCrmAuth(CrmEnum.CUSTOMER, customer.getCustomerId());
                                if (auth) {
                                    rowList.add(0, "数据无覆盖权限");
                                    errorList.add(rowList);
                                    return;
                                }
                                entityObject.put("customerId", customer.getCustomerId());
                                entityObject.put("batchId", customer.getBatchId());
                            }
                            object.setField(addFieldArray(rowList));
                            object.setEntity(entityObject);
                            CrmVerify verify = new CrmVerify(getUploadExcelBO().getCrmEnum());
                            Result result = verify.verify(object);
                            if (!result.hasSuccess()) {
                                rowList.add(0, result.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            try {
                                SpringUtils.getBean(ICrmCustomerService.class).addOrUpdate(object, true,getUploadExcelBO().getPoolId());
                            } catch (CrmException e) {
                                rowList.add(0, e.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            if (isUpdate) {
                                updateNum++;
                            }
                        } catch (Exception ex) {
                            log.error("导入数据异常:", ex);
                            rowList.add(0, "导入异常");
                            errorList.add(rowList);
                        }
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }


    public class StudentUploadService extends UploadService {
        private UploadExcelBO uploadExcelBO;

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }

        private StudentUploadService(UploadExcelBO uploadExcelBO){
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel(){
            ExcelUtil.readBySax(uploadExcelBO.getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size(); i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        importStudents(rowList);
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        public synchronized void importStudents(List<Object> rowList){
            List<CrmStudent> studentList = new ArrayList<>();
            QueryWrapper<CrmStudent> queryWrapper = new QueryWrapper<>();
            queryWrapper.ne("status", 3);
            queryWrapper.eq("assistant_user_id",UserUtil.getUserId());
            List<CrmStudent> students = SpringUtils.getBean(ICrmStudentService.class).getBaseMapper().selectList(queryWrapper);
            for(CrmModelFiledVO filedVO : fieldList){
                if("telephone".equals(filedVO.getFieldName())){
                    String telephone = String.valueOf(rowList.get(kv.getInteger(filedVO.getFieldName()))).trim();
                    if(StringUtils.isNotEmpty(telephone)){
                        Optional<CrmStudent> first = students.stream().filter(item -> telephone.equals(item.getTelephone())).findFirst();
                        first.ifPresent(studentList::add);
                    }
                }else if("wechat".equals(filedVO.getFieldName())){
                    String wechat = String.valueOf(rowList.get(kv.getInteger(filedVO.getFieldName()))).trim();
                    if(StringUtils.isNotEmpty(wechat)){
                        Optional<CrmStudent> first = students.stream().filter(item -> wechat.equals(item.getWechat())).findFirst();
                        first.ifPresent(studentList::add);
                    }
                }
            }

            CrmModelSaveBO object = new CrmModelSaveBO();
            JSONObject entityObject = new JSONObject();

            if (studentList.size() > 0) {
                CrmStudent student = studentList.get(0);
                boolean auth = AuthUtil.isCrmAuth(CrmEnum.STUDENT, student.getStudentId());
                if (auth) {
                    rowList.add(0, "数据无覆盖权限");
                    errorList.add(rowList);
                    return;
                }
                entityObject.fluentPutAll(JSONObject.parseObject(JSONObject.toJSONString(student)));
//                entityObject.put("studentId", student.getStudentId());
//                entityObject.put("batchId", student.getBatchId());
            }else{
                rowList.add(0,"未匹配到对应手机号/微信号的学员");
                errorList.add(rowList);
                return;
            }

            fixedFieldList.forEach(field -> {
                String fieldName = field.getFieldName();
                if(!"leadsCreateTime".equals(field.getFieldName())){
                    entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)) == null ? "" : String.valueOf(rowList.get(kv.getInteger(fieldName))).trim());
                }else{
                    entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                }
            });

            object.setField(addFieldArray(rowList));
            object.setEntity(entityObject);

            CrmVerify verify = new CrmVerify(getUploadExcelBO().getCrmEnum());
            Result result = verify.verify(object);
            if (!result.hasSuccess()) {
                rowList.add(0, result.getMsg());
                errorList.add(rowList);
                return;
            }

            try {
                if(entityObject.get("studentId") != null){
                    SpringUtils.getBean(ICrmStudentService.class).addOrUpdate(object, true, null);
                }
            } catch (CrmException ex) {
                rowList.add(0, ex.getMsg());
                errorList.add(rowList);
                return;
            }
            if(entityObject.get("studentId") != null){
                updateNum++;
            }
        }
    }


    public  class LeadsUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private LeadsUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public  void importExcel() {
            ExcelUtil.readBySax(uploadExcelBO.getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size(); i < kv.entrySet().size(); i++) {
                        rowList.add("");
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    log.info("CrmUploadExcelServiceImpl.importExcel.templateErr:{}",templateErr);
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        importLeads(rowList);
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        public synchronized void importLeads(List<Object> rowList){
            String option="";
            String optionSignClass="";
            String optionAddWechat="";
            List<CrmModelFiledVO> crmModelFiledVOS = fieldService.queryFieldSource("source");
            for(CrmModelFiledVO c:crmModelFiledVOS){
                option= c.getOptions();
            }
            String[] option_split = option.split(",");
            String telephone = "";
            String wechat = "";
            AtomicReference<String> source= new AtomicReference<>("");
            AtomicReference<String> salesType= new AtomicReference<>("");
            AtomicReference<String> signClass= new AtomicReference<>("");
            AtomicReference<String> addWechat= new AtomicReference<>("");
            AtomicReference<String> classes= new AtomicReference<>("");
            String telephoneValue = UUID.randomUUID().toString();
            String wechatValue = UUID.randomUUID().toString();
            for (CrmModelFiledVO record : uniqueList) {
                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                rowList.set(kv.getInteger(record.getFieldName()), String.valueOf(value).trim());
                record.setValue(value);
                if("telephone".equals(record.getFieldName())){
                    telephone = String.valueOf(value).trim();
                }else if("wechat".equals(record.getFieldName())){
                    wechat = String.valueOf(value).trim();
                }

            }

            //训练营期数
            AtomicReference<String> trainingPeriods= new AtomicReference<>("");

            fieldList.forEach(record -> {
                if (record.getFieldType().equals(1)) {
                    if ("source".equals(record.getFieldName())){
                        Integer columnsNum = kv.getInteger(record.getFieldName());
                        Object value = rowList.get(columnsNum);
                        source.set(String.valueOf(value).trim());
                    }
                    if("signClass".equals(record.getFieldName())){
                        Integer columnsNum = kv.getInteger(record.getFieldName());
                        Object value = rowList.get(columnsNum);
                        signClass.set(String.valueOf(value).trim());
                    }
                    if("addWechat".equals(record.getFieldName())){
                        Integer columnsNum = kv.getInteger(record.getFieldName());
                        Object value = rowList.get(columnsNum);
                        addWechat.set(String.valueOf(value).trim());
                    }
                    if("trainingPeriods".equals(record.getFieldName())){
                        Integer columnsNum = kv.getInteger(record.getFieldName());
                        Object value = rowList.get(columnsNum);
                        trainingPeriods.set(String.valueOf(value).trim());

                    }
                }
            });

            //判断训练营期数是否正确
            Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
            if(!pattern.matcher(trainingPeriods.get()).matches()){
                Integer leadsCreateTime = (Integer) kv.get("leadsCreateTime");
                Integer addWechatTime = (Integer) kv.get("addWechatTime");
                Integer signClassTime = (Integer) kv.get("signClassTime");
                if(!verifyDate(String.valueOf(rowList.get(leadsCreateTime))).hasSuccess()){
                    rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                    errorList.add(rowList);
                    return;
                }
                rowList.add(0, "训练营期数填写错误,格式应为纯数字。");
                errorList.add(rowList);
                return;
            }

            //是否加微信判断
            List<CrmModelFiledVO> optionAddWechatModel = fieldService.queryFieldSource("add_wechat");
            for(CrmModelFiledVO c:optionAddWechatModel){
                optionAddWechat = c.getOptions();
            }
            String[] optionAddWechat_split = optionAddWechat.split(",");
            Boolean wechatVerify = false;
            if(StringUtils.isNotEmpty(addWechat.get())){
                for (String op:optionAddWechat_split){
                    if (op.equals(addWechat.get())){
                        wechatVerify=true;
                        break;
                    }
                }
            }else{
                wechatVerify = true;
            }
            if (wechatVerify==false){
                Integer leadsCreateTime = (Integer) kv.get("leadsCreateTime");
                Integer addWechatTime = (Integer) kv.get("addWechatTime");
                Integer signClassTime = (Integer) kv.get("signClassTime");
                if(!verifyDate(String.valueOf(rowList.get(leadsCreateTime))).hasSuccess()){
                    rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                    errorList.add(rowList);
                    return;
                }
                rowList.add(0, "是否加微信填写错误");

                errorList.add(rowList);
                return;
            }

            //是否报课判断
            List<CrmModelFiledVO> optionSignClassModel = fieldService.queryFieldSource("sign_class");
            for(CrmModelFiledVO c:optionSignClassModel){
                optionSignClass = c.getOptions();
            }
            String[] optionSignClass_split = optionSignClass.split(",");
            Boolean sign = false;
            if(StringUtils.isNotEmpty(signClass.get())){
                for (String op:optionSignClass_split){
                    if (op.equals(signClass.get())){
                        sign=true;
                        break;
                    }
                }
            }else{
                sign=true;
            }

            if (sign==false){

                Integer leadsCreateTime = (Integer) kv.get("leadsCreateTime");
                Integer addWechatTime = (Integer) kv.get("addWechatTime");
                Integer signClassTime = (Integer) kv.get("signClassTime");
                if(!verifyDate(String.valueOf(rowList.get(leadsCreateTime))).hasSuccess()){
                    rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                    errorList.add(rowList);
                    return;
                }
                rowList.add(0, "是否报课填写错误");

                errorList.add(rowList);
                return;
            }

            Integer leadsCreateTime = (Integer) kv.get("leadsCreateTime");
            // 日期填写错误
            Integer addWechatTime = (Integer) kv.get("addWechatTime");
            Integer signClassTime = (Integer) kv.get("signClassTime");

            String signClass1 = (String) rowList.get(signClassTime);
            String addWechat1 = (String) rowList.get(addWechatTime);

//            Date signClassDateTime = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) rowList.get(signClassTime));
            if(StringUtils.isNotEmpty(signClass1) && (DatePattern.NORM_DATETIME_PATTERN.length() != signClass1.length() || signClass1.split(" ").length != 2)){

                rowList.add(0, "报课时间" + CrmCodeEnum.CRM_DATETIME_FORMAT_ERROR.getMsg());
                errorList.add(rowList);
                return;
            }
//            Date addWechatDateTime = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) rowList.get(addWechatTime));
//            if(!DateUtils.YYYY_MM_DD_HH_MM_SS.equals(DateUtil.formatDate(addWechatDateTime))){
            if(StringUtils.isNotEmpty(addWechat1) && (DatePattern.NORM_DATETIME_PATTERN.length() != addWechat1.length() || addWechat1.split(" ").length != 2)){

                rowList.add(0, "加微信时间" + CrmCodeEnum.CRM_DATETIME_FORMAT_ERROR.getMsg());
                errorList.add(rowList);

                return;
            }


            for (Integer integer : isNullList) {
                if (ObjectUtil.isEmpty(rowList.get(integer))) {
                    rowList.add(0, "必填字段未填写");
                    errorList.add(rowList);
                    return;
                }
            }

            try {
                if(!telephone.equals(wechat)){
                    if(StringUtils.isNotEmpty(telephone) && StringUtils.isNotEmpty(wechat)){
                        Boolean telephoneResult = redisImpl.lock(telephone,telephoneValue,3, TimeUnit.SECONDS);
                        Boolean wechatResult = redisImpl.lock(wechat,wechatValue,3, TimeUnit.SECONDS);
                        if(!telephoneResult || !wechatResult){
                            System.out.println("telephoneResult || wechatValue");
                            return;
                        }
                        leadsDataImport(rowList);
                    }else if(StringUtils.isNotEmpty(telephone) && StringUtils.isEmpty(wechat)){
                        Boolean telephoneResult = redisImpl.lock(telephone,telephoneValue,3, TimeUnit.SECONDS);
                        if(!telephoneResult){
                            System.out.println("telephoneResult");
                            return;
                        }
                        leadsDataImport(rowList);
                    }else if(StringUtils.isEmpty(telephone) && StringUtils.isNotEmpty(wechat)){
                        Boolean wechatResult = redisImpl.lock(wechat,wechatValue,3, TimeUnit.SECONDS);
                        if(!wechatResult){
                            System.out.println("wechatValue");
                            return;
                        }
                        leadsDataImport(rowList);
                    }
                }else{
                    Boolean telephoneResult = redisImpl.lock(telephone,telephoneValue,3, TimeUnit.SECONDS);
                    if(!telephoneResult){
                        System.out.println("telephone == wechat");
                        return;
                    }
                    leadsDataImport(rowList);
                }

            } catch (Exception ex) {
                log.error("导入数据异常:", ex);
                rowList.add(0, "导入异常");
                errorList.add(rowList);
            } finally {
                if(StringUtils.isNotEmpty(telephoneValue) && StringUtils.isNotEmpty(wechatValue)){
                    redisImpl.unlock(telephone,telephoneValue);
                    redisImpl.unlock(wechat,wechatValue);
                }else if(StringUtils.isNotEmpty(telephoneValue) && StringUtils.isEmpty(wechatValue)){
                    redisImpl.unlock(telephone,telephoneValue);
                }else if(StringUtils.isEmpty(telephoneValue) && StringUtils.isNotEmpty(wechatValue)){
                    redisImpl.unlock(wechat,wechatValue);
                }

            }
        }

        @Transactional(rollbackFor = Exception.class)
        public void leadsDataImport(List<Object> rowList){
//            List<CrmLeads> leadsList = uniqueMapList(uniqueList).stream().map(map -> BeanUtil.mapToBean(map, CrmLeads.class, true)).collect(Collectors.toList());
            List<CrmLeads> leadsList = new ArrayList<>();
            for(CrmModelFiledVO crmModelFiledVO:uniqueList){
                if("telephone".equals(crmModelFiledVO.getFieldName())){
                    if(StringUtils.isNotEmpty(String.valueOf(crmModelFiledVO.getValue()))){
                        leadsList.addAll(SpringUtils.getBean(CrmLeadsServiceImpl.class).queryLeadsList(String.valueOf(crmModelFiledVO.getValue()).trim()));
                    }
                }else if("wechat".equals(crmModelFiledVO.getFieldName())){
                    if(StringUtils.isNotEmpty(String.valueOf(crmModelFiledVO.getValue()))){
                        leadsList.addAll(SpringUtils.getBean(CrmLeadsServiceImpl.class).queryLeadsList1(String.valueOf(crmModelFiledVO.getValue()).trim()));
                    }
                }
            }
            boolean isUpdate = false;
            if (leadsList.size() > 0) {
                if (Objects.equals(2, uploadExcelBO.getRepeatHandling())) {
                    if (leadsList.size() > 0) {
                        Integer leadsCreateTime = (Integer) kv.get("leadsCreateTime");
                        if(!verifyDate(String.valueOf(rowList.get(leadsCreateTime))).hasSuccess()){
                            rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                            errorList.add(rowList);
                            return;
                        }
                        rowList.add(0, "唯一性字段重复数据");
                        errorList.add(rowList);
                        return;
                    }
                }
                isUpdate = true;
            }
            CrmModelSaveBO object = new CrmModelSaveBO();
            JSONObject entityObject = new JSONObject();
            fixedFieldList.forEach(field -> {
                String fieldName = field.getFieldName();
                if(!"leadsCreateTime".equals(field.getFieldName())){
                    entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)) == null ? null : String.valueOf(rowList.get(kv.getInteger(fieldName))).trim());
                }else{
                    entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                }
            });

            //判断日期是否正确
            if(verifyDate(String.valueOf(entityObject.get("leadsCreateTime"))).hasSuccess()){
                String leadsCreateTime = String.valueOf(entityObject.get("leadsCreateTime"));
                if(Validator.isMatchRegex(Pattern.compile("^\\d{4}-\\d{2}-\\d{2}$"), leadsCreateTime)){
                    Date date = DateUtils.parseDate(leadsCreateTime);
                    entityObject.fluentPut("leadsCreateTime",date);
                }else{
                    rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                    errorList.add(rowList);
                    return;
                }
//                entityObject.fluentPut("leadsCreateTime",DateUtil.formatDate((Date) entityObject.get("leadsCreateTime")));
            }else{
                rowList.add(0, "获取表单时间" + CrmCodeEnum.CRM_DATE_FORMAT_ERROR.getMsg());
                errorList.add(rowList);
                return;
//                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) entityObject.get("leadsCreateTime"));
//                entityObject.fluentPut("leadsCreateTime",DateUtil.formatDate(date));
            }

            entityObject.fluentPut("ownerUserId", uploadExcelBO.getOwnerUserId());
            entityObject.fluentPut("leadsUserId", uploadExcelBO.getLeadsUserId());
            if (leadsList.size() == 1) {
                if(!Objects.equals(3, uploadExcelBO.getRepeatHandling())){
                    CrmLeads leads = leadsList.get(0);
                    boolean auth = AuthUtil.isCrmAuth(CrmEnum.LEADS, leads.getLeadsId());
                    if (auth) {
                        rowList.add(0, "数据无覆盖权限");
                        errorList.add(rowList);
                        return;
                    }
                    entityObject.put("leadsId", leads.getLeadsId());
                    entityObject.put("batchId", leads.getBatchId());
                }
            }
            object.setField(addFieldArray(rowList));
            /*//销售类型，字段值修改
            if(SalesTypeEnum.LEADS.getRemarks().equals(entityObject.get("salesType"))){
                entityObject.put("salesType",SalesTypeEnum.LEADS.getType());
            }else if(SalesTypeEnum.MAIL.getRemarks().equals(entityObject.get("salesType"))){
                entityObject.put("salesType",SalesTypeEnum.MAIL.getType());
            }*/

            object.setEntity(entityObject);
            CrmVerify verify = new CrmVerify(getUploadExcelBO().getCrmEnum());
            Result result = verify.verify(object);
            if (!result.hasSuccess()) {
                rowList.add(0, result.getMsg());
                errorList.add(rowList);
                return;
            }
            try {
                log.info("CrmUploadExcelServiceImpl.leadsDataImport.object:{}",JSONObject.toJSONString(object));
                SpringUtils.getBean(ICrmLeadsService.class).addOrUpdate(object, true);
            } catch (CrmException ex) {
                rowList.add(0, ex.getMsg());
                errorList.add(rowList);
                return;
            }
            if (isUpdate) {
                updateNum++;
            }
        }

        /**
         * 验证日期是否正确
         * @param str
         * @return
         */
        private Result verifyDate(String str) {
            if (!isTime(str)) {
                return R.error(CrmCodeEnum.CRM_DATE_FORMAT_ERROR);
            }
            return R.ok();
        }

        private boolean isTime(String str) {
            if (Validator.isMatchRegex(Validator.BIRTHDAY, str)) {
                Matcher matcher = Validator.BIRTHDAY.matcher(str);
                if (matcher.find()) {
                    int year = Integer.parseInt(matcher.group(1));
                    int month = Integer.parseInt(matcher.group(3));
                    int day = Integer.parseInt(matcher.group(5));
                    // 验证月
                    if (month < 1 || month > 12) {
                        return false;
                    }

                    // 验证日
                    if (day < 1 || day > 31) {
                        return false;
                    }
                    // 检查几个特殊月的最大天数
                    if (day == 31 && (month == 4 || month == 6 || month == 9 || month == 11)) {
                        return false;
                    }
                    if (month == 2) {
                        // 在2月，非闰年最大28，闰年最大29
                        return day < 29 || (day == 29 && DateUtil.isLeapYear(year));
                    }
                    return true;
                }
            }
            return false;
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }

    public class ContactsUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private ContactsUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel() {

            Map<String, List<String>> areaMap = CrmExcelUtil.getAreaMap();

            ExcelUtil.readBySax(uploadExcelBO.getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size() - 1; i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        try {
//                            String customerName = rowList.get(kv.getInteger("customerId")).toString();
                            String serialNumber = rowList.get(kv.getInteger("serialNumber")).toString();
                            SimpleCrmEntity customer = SpringUtils.getBean(ICrmCustomerService.class).queryCustomerByNum(serialNumber);
                            if (customer == null) {
                                rowList.add(0, "填写的客户不存在");
                                errorList.add(rowList);
                                return;
                            }
                            for (Integer integer : isNullList) {

                                if (integer!=40&&ObjectUtil.isEmpty(rowList.get(integer))) {
                                    rowList.add(0, "必填字段未填写");
                                    errorList.add(rowList);
                                    return;
                                }
                            }
                            for (CrmModelFiledVO record : uniqueList) {
                                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                                record.setValue(value);
                            }
                            List<CrmContacts> contactsList = uniqueMapList(uniqueList).stream().map(map -> BeanUtil.mapToBean(map, CrmContacts.class, true)).collect(Collectors.toList());
                            boolean isUpdate = false;
                            if (contactsList.size() > 0) {
                                if (Objects.equals(2, uploadExcelBO.getRepeatHandling())) {
                                    return;
                                }
                                if (contactsList.size() > 1) {
                                    rowList.add(0, "数据与多条唯一性字段重复");
                                    errorList.add(rowList);
                                    return;
                                }
                                isUpdate = true;
                            }
                            CrmContactsSaveBO object = new CrmContactsSaveBO();
                            JSONObject entityObject = new JSONObject();
                            fixedFieldList.forEach(field -> {
                                if (!field.getFieldName().equals("customerId")) {
                                    String fieldName = field.getFieldName();
                                    if("telephone".equals(fieldName) || "qq".equals(fieldName)){
                                        entityObject.fluentPut(fieldName, String.valueOf(rowList.get(kv.getInteger(fieldName))).trim());
                                    }else{
                                        entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                                    }
                                }
                            });
                            entityObject
                                    .fluentPut("customerId", customer.getId())
                                    .fluentPut("ownerUserId", getUploadExcelBO().getOwnerUserId());

                            String province = ObjectUtil.isEmpty(rowList.get(kv.getInteger("province"))) ? "" : rowList.get(kv.getInteger("province")).toString();
                            String city = ObjectUtil.isEmpty(rowList.get(kv.getInteger("city"))) ? "" : rowList.get(kv.getInteger("city")).toString();
                            String site = ObjectUtil.isEmpty(rowList.get(kv.getInteger("site"))) ? "" : rowList.get(kv.getInteger("site")).toString();
                            if (StrUtil.isNotEmpty(city) && (!areaMap.containsKey(province) || !areaMap.get(province).contains(city))) {
                                rowList.add(0, "省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }
                            if (StrUtil.isNotEmpty(site) && (!areaMap.containsKey(city) || !areaMap.get(city).contains(site))) {
                                rowList.add(0, "省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }

                            //目前就业城市
                            String currentProvince = ObjectUtil.isEmpty(rowList.get(kv.getInteger("currentProvince"))) ? "" : rowList.get(kv.getInteger("currentProvince")).toString();
                            String currentCity = ObjectUtil.isEmpty(rowList.get(kv.getInteger("currentCity"))) ? "" : rowList.get(kv.getInteger("currentCity")).toString();
                            String currentSite = ObjectUtil.isEmpty(rowList.get(kv.getInteger("currentSite"))) ? "" : rowList.get(kv.getInteger("currentSite")).toString();
                            if (StrUtil.isNotEmpty(currentCity) && (!areaMap.containsKey(currentProvince) || !areaMap.get(currentProvince).contains(currentCity))) {
                                rowList.add(0, "目前就业省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }
                            if (StrUtil.isNotEmpty(currentSite) && (!areaMap.containsKey(currentCity) || !areaMap.get(currentCity).contains(currentSite))) {
                                rowList.add(0, "目前就业省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }

                            //期望就业城市
                            String qiWangProvince = ObjectUtil.isEmpty(rowList.get(kv.getInteger("qiWangProvince"))) ? "" : rowList.get(kv.getInteger("qiWangProvince")).toString();
                            String qiWangCity = ObjectUtil.isEmpty(rowList.get(kv.getInteger("qiWangCity"))) ? "" : rowList.get(kv.getInteger("qiWangCity")).toString();
                            String qiWangSite = ObjectUtil.isEmpty(rowList.get(kv.getInteger("qiWangSite"))) ? "" : rowList.get(kv.getInteger("qiWangSite")).toString();
                            if (StrUtil.isNotEmpty(qiWangCity) && (!areaMap.containsKey(qiWangProvince) || !areaMap.get(qiWangProvince).contains(qiWangCity))) {
                                rowList.add(0, "目前就业省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }
                            if (StrUtil.isNotEmpty(qiWangSite) && (!areaMap.containsKey(qiWangCity) || !areaMap.get(qiWangCity).contains(qiWangSite))) {
                                rowList.add(0, "目前就业省市区不合要求，请核对");
                                errorList.add(rowList);
                                return;
                            }



                            fixedFieldList.forEach(field -> {
                                if (!field.getFieldName().equals("customerId")) {
                                    String fieldName = field.getFieldName();
                                    if("telephone".equals(fieldName) || "qq".equals(fieldName)){
                                        entityObject.fluentPut(fieldName, String.valueOf(rowList.get(kv.getInteger(fieldName))).trim());
                                    }else{
                                        entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                                    }
                                }
                            });

                            entityObject.fluentPut("address", StrUtil.isNotEmpty(province) ? "{\"province\":\""+province+"\",\"city\":\""+city+"\",\"area\":\""+site+"\"}" : "")
                                    .fluentPut("currentCity", StrUtil.isNotEmpty(currentProvince) ? "{\"province\":\""+currentProvince+"\",\"city\":\""+currentCity+"\",\"area\":\""+currentSite+"\"}" : "")
                                    //.fluentPut("address", StrUtil.isNotEmpty(qiWangProvince) ? "{\"province\":\""+qiWangProvince+"\",\"city\":\""+qiWangCity+"\",\"area\":\""+qiWangSite+"\"}" : "")
                                    .fluentPut("ownerUserId", getUploadExcelBO().getOwnerUserId());
                            if (contactsList.size() == 1) {
                                CrmContacts contacts = contactsList.get(0);
                                boolean auth = AuthUtil.isCrmAuth(CrmEnum.CONTACTS, contacts.getContactsId());
                                if (auth) {
                                    rowList.add(0, "数据无覆盖权限");
                                    errorList.add(rowList);
                                    return;
                                }
                                entityObject.put("contacts_id", contacts.getContactsId());
                                entityObject.put("batch_id", contacts.getBatchId());
                            }
                            object.setField(addFieldArrayContacts(rowList));
                            object.setEntity(entityObject);
                            CrmVerify verify = new CrmVerify(getUploadExcelBO().getCrmEnum());
                            Result result = verify.verify(object);
                            if (!result.hasSuccess()) {
                                rowList.add(0, result.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            try {
                                SpringUtils.getBean(CrmContactsServiceImpl.class).addOrUpdateToImport(object, true);
                            } catch (CrmException ex) {
                                rowList.add(0, ex.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            if (isUpdate) {
                                updateNum++;
                            }
                        } catch (Exception ex) {
                            log.error("导入数据异常:", ex);
                            rowList.add(0, "导入异常");
                            errorList.add(rowList);
                        }
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        private List<CrmModelFiledVO> addFieldArrayContacts(List<Object> rowList) {
            List<CrmModelFiledVO> array = new ArrayList<>();
            fieldList.forEach(record -> {
                if (record.getFieldType().equals(0) || record.getFieldType().equals(2)) {
                    Integer columnsNum = kv.getInteger(record.getFieldName());
                    Object value = rowList.get(columnsNum);
                    if (Objects.equals(record.getType(), 4) && value instanceof Long) {
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) value);
                        record.setValue(DateUtil.formatDate(date));
                    } else {
                        record.setValue(value);
                    }
                    array.add(record);
                }
                else{
                    if (record.getFieldName().equals("contactsType")){
                        array.add(record.setValue(""));
                    }
                }
            });
            return array;
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }

    public class ProductUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private ProductUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel() {
            ExcelUtil.readBySax(getUploadExcelBO().getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size() - 1; i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        try {
                            String categoryName = rowList.get(kv.getInteger("categoryId")).toString();
                            CrmProductCategory category = SpringUtils.getBean(ICrmProductCategoryService.class).queryFirstCategoryByName(categoryName);
                            if (category == null) {
                                rowList.add(0, "填写的产品类型不存在");
                                errorList.add(rowList);
                                return;
                            }
                            for (Integer integer : isNullList) {
                                if (ObjectUtil.isEmpty(rowList.get(integer))) {
                                    rowList.add(0, "必填字段未填写");
                                    errorList.add(rowList);
                                    return;
                                }
                            }
                            for (CrmModelFiledVO record : uniqueList) {
                                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                                record.setValue(value);
                            }
                            List<CrmProduct> productList = uniqueMapList(uniqueList).stream().map(map -> BeanUtil.mapToBean(map, CrmProduct.class, true)).collect(Collectors.toList());
                            boolean isUpdate = false;
                            if (productList.size() > 0) {
                                if (Objects.equals(2, uploadExcelBO.getRepeatHandling())) {
                                    return;
                                }
                                if (productList.size() > 1) {
                                    rowList.add(0, "数据与多条唯一性字段重复");
                                    errorList.add(rowList);
                                    return;
                                }
                                isUpdate = true;
                            }
                            CrmModelSaveBO object = new CrmModelSaveBO();
                            JSONObject entityObject = new JSONObject();
                            if (productList.size() == 1) {
                                CrmProduct product = productList.get(0);
                                boolean auth = AuthUtil.isCrmAuth(CrmEnum.PRODUCT, product.getProductId());
                                if (auth) {
                                    rowList.add(0, "数据无覆盖权限");
                                    errorList.add(rowList);
                                    return;
                                }
                                entityObject.put("product_id", product.getProductId());
                                entityObject.put("batch_id", product.getBatchId());
                            }
                            fixedFieldList.forEach(field -> {
                                if (!field.getFieldName().equals("categoryId")) {
                                    String fieldName = field.getFieldName();
                                    if (!"status".equals(fieldName)) {
                                        entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                                    } else {
                                        Object value = rowList.get(kv.getInteger(fieldName));
                                        entityObject.fluentPut(fieldName, "上架".equals(value) ? 1 : 0);
                                    }

                                }
                            });
                            entityObject.fluentPut("categoryId", category.getCategoryId()).fluentPut("ownerUserId", uploadExcelBO.getOwnerUserId());
                            object.setField(addFieldArray(rowList));
                            object.setEntity(entityObject);
                            CrmVerify verify = new CrmVerify(uploadExcelBO.getCrmEnum());
                            Result result = verify.verify(object);
                            if (!result.hasSuccess()) {
                                rowList.add(0, result.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            try {
                                SpringUtils.getBean(ICrmProductService.class).addOrUpdate(object, true);
                            } catch (CrmException ex) {
                                rowList.add(0, ex.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            if (isUpdate) {
                                updateNum++;
                            }
                        } catch (Exception ex) {
                            log.error("导入数据异常:", ex);
                            rowList.add(0, "导入异常");
                            errorList.add(rowList);
                        }
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }


    public class RoiInvestmentUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private RoiInvestmentUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel() {
            ExcelUtil.readBySax(getUploadExcelBO().getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size() - 1; i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        try {
                            for (Integer integer : isNullList) {
                                if (ObjectUtil.isEmpty(rowList.get(integer))) {
                                    rowList.add(0, "必填字段未填写");
                                    errorList.add(rowList);
                                    return;
                                }
                            }
                            for (CrmModelFiledVO record : uniqueList) {
                                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                                record.setValue(value);
                            }
                            List<CrmPutinMoney> crmPutinMoneyList = uniqueMapListMonery(uniqueList);
                            boolean isUpdate = false;
                            if (crmPutinMoneyList.size() > 0) {
                                if (Objects.equals(2, uploadExcelBO.getRepeatHandling())) {
                                    return;
                                }
                                if (crmPutinMoneyList.size() > 1) {
                                    rowList.add(0, "数据与多条唯一性字段重复");
                                    errorList.add(rowList);
                                    return;
                                }
                                isUpdate = true;
                            }
                            CrmPutinMoneySaveBO object = new CrmPutinMoneySaveBO();
                            JSONObject entityObject = new JSONObject();
                            if (crmPutinMoneyList.size() == 1) {
                                CrmPutinMoney money = crmPutinMoneyList.get(0);
//                                boolean auth = AuthUtil.isCrmAuth(CrmEnum.PRODUCT, money.getProductId());
//                                if (auth) {
//                                    rowList.add(0, "数据无覆盖权限");
//                                    errorList.add(rowList);
//                                    return;
//                                }
                                entityObject.put("putinMoneyId", money.getPutinMoneyId());
//                                entityObject.put("batch_id", product.getBatchId());
                            }
                            fixedFieldList.forEach(field -> {
                                if (!field.getFieldName().equals("categoryId")) {
                                    String fieldName = field.getFieldName();
                                    if (!"status".equals(fieldName)) {
                                        entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                                    } else {
                                        Object value = rowList.get(kv.getInteger(fieldName));
                                        entityObject.fluentPut(fieldName, "上架".equals(value) ? 1 : 0);
                                    }

                                }
                            });
                            object.setField(addFieldArray(rowList));
                            object.setEntity(entityObject);
                            CrmVerify verify = new CrmVerify(uploadExcelBO.getCrmEnum());
                            Result result = verify.verify(object);
                            if (!result.hasSuccess()) {
                                rowList.add(0, result.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            try {
                                SpringUtils.getBean(ICrmPutinMoneyService.class).addOrUpdate(object);
                            } catch (CrmException ex) {
                                rowList.add(0, ex.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            if (isUpdate) {
                                updateNum++;
                            }
                        } catch (Exception ex) {
                            log.error("导入数据异常:", ex);
                            rowList.add(0, "导入异常");
                            errorList.add(rowList);
                        }
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }


    public class BillReceivablesUploadService extends UploadService {

        private UploadExcelBO uploadExcelBO;

        private BillReceivablesUploadService(UploadExcelBO uploadExcelBO) {
            this.uploadExcelBO = uploadExcelBO;
        }

        @Override
        public void importExcel() {
            ExcelUtil.readBySax(getUploadExcelBO().getFilePath(), 0, (int sheetIndex, int rowIndex, List<Object> rowList) -> {
                num++;
                redis.setex(AdminCacheKey.UPLOAD_EXCEL_MESSAGE_PREFIX + getUploadExcelBO().getMessageId().toString(), UPLOAD_EXCEL_EXIST_TIME, Math.max(num, 0));
                if (rowList.size() < kv.entrySet().size()) {
                    for (int i = rowList.size() - 1; i < kv.entrySet().size(); i++) {
                        rowList.add(null);
                    }
                }
                if (num > 10001) {
                    rowList.add(0, "最多同时导入10000条数据");
                    errorList.add(rowList);
                    return;
                }
                if (rowIndex > 1) {
                    if (templateErr) {
                        rowList.add(0, "请使用最新的模板");
                        errorList.add(rowList);
                    } else {
                        try {
                            for (Integer integer : isNullList) {
                                if (ObjectUtil.isEmpty(rowList.get(integer))) {
                                    rowList.add(0, "必填字段未填写");
                                    errorList.add(rowList);
                                    return;
                                }
                            }
                            for (CrmModelFiledVO record : uniqueList) {
                                Object value = rowList.get(kv.getInteger(record.getFieldName()));
                                record.setValue(value);
                            }
//                            List<Map<String, Object>> crmOverdueList = uniqueMapList(uniqueList);
                            boolean isUpdate = true;
//                            if (crmOverdueList.size() > 0) {
//                                if (Objects.equals(2, uploadExcelBO.getRepeatHandling())) {
//                                    return;
//                                }
//                                if (crmOverdueList.size() > 1) {
//                                    rowList.add(0, "数据与多条唯一性字段重复");
//                                    errorList.add(rowList);
//                                    return;
//                                }
//                                isUpdate = true;
//                            }
                            CrmContractSaveBO object = new CrmContractSaveBO();
                            JSONObject entityObject = new JSONObject();
                            fixedFieldList.forEach(field -> {
                                String fieldName = field.getFieldName();
                                entityObject.fluentPut(fieldName, rowList.get(kv.getInteger(fieldName)));
                            });
                            object.setField(addFieldArray(rowList));
                            object.setEntity(entityObject);
                            CrmVerify verify = new CrmVerify(uploadExcelBO.getCrmEnum());
                            Result result = verify.verify(object);
                            if (!result.hasSuccess()) {
                                rowList.add(0, result.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            try {
                                log.info("object:"+ JSONObject.toJSONString(object));
                                SpringUtils.getBean(ICrmReceivablesService.class).importBillData(object);
                            } catch (CrmException ex) {
                                isUpdate = false;
                                rowList.add(0, ex.getMsg());
                                errorList.add(rowList);
                                return;
                            }
                            if (isUpdate) {
                                updateNum++;
                            }
                        } catch (Exception ex) {
                            log.error("导入数据异常:", ex);
                            rowList.add(0, "导入异常");
                            errorList.add(rowList);
                        }
                    }
                } else if (rowIndex == 1) {
                    queryExcelHead(rowList);
                } else {
                    errorList.add(0, rowList);
                }
            });
        }

        private List<CrmModelFiledVO> addFieldArrayContacts(List<Object> rowList) {
            List<CrmModelFiledVO> array = new ArrayList<>();
            fieldList.forEach(record -> {
                if (record.getFieldType().equals(0) || record.getFieldType().equals(2)) {
                    Integer columnsNum = kv.getInteger(record.getFieldName());
                    Object value = rowList.get(columnsNum);
                    if (Objects.equals(record.getType(), 4) && value instanceof Long) {
                        Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate((Long) value);
                        record.setValue(DateUtil.formatDate(date));
                    } else {
                        record.setValue(value);
                    }
                    array.add(record);
                }
                else{
                    if (record.getFieldName().equals("contactsType")){
                        array.add(record.setValue(""));
                    }
                }
            });
            return array;
        }

        @Override
        public UploadExcelBO getUploadExcelBO() {
            return uploadExcelBO;
        }
    }
}
